﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data;
using System.Data.SqlClient;

namespace University.Workshop.DataAccess
{
    public static class ClientDataAccess
    {
        const string ConexionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";


        public static bool RegisterClient(Client client)
        {
            string sqlSentence = "INSERT INTO Client VALUES(@nit,@name,@surname1,@surname2,@phone,@cellphone,@email,@address,@state)";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            int result = 0;
            if (client == null) {
                throw new BusinessException("Se quiso insertar un cliente nulo");
            }
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@nit", client.Nit);
                cmd.Parameters.AddWithValue("@name", client.Name);
                cmd.Parameters.AddWithValue("@surname1", client.FatherLastName);
                cmd.Parameters.AddWithValue("@surname2", client.MotherLastName);
                cmd.Parameters.AddWithValue("@phone", client.Phone);
                cmd.Parameters.AddWithValue("@cellphone", client.CellPhone);
                cmd.Parameters.AddWithValue("@email", client.Email);
                cmd.Parameters.AddWithValue("@address", client.Address);
                cmd.Parameters.AddWithValue("@state", client.State);
                result = cmd.ExecuteNonQuery();
                return result != 0;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool ReviewIdIfNitRegister(string nit)
        {
            string sqlSentence = "SELECT NIT From [Client] WHERE NIT = @nit";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@nit", Convert.ToInt32(nit, System.Globalization.CultureInfo.CurrentCulture));
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                    return true;
                else
                    return false;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool DeleteClientData(string value)
        {
            string sqlSentence = "UPDATE Client SET State = 'I' WHERE Nit = @code";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return !reader.HasRows;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static bool ModifyNameClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Name = @codes WHERE Nit = @code";
           
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return (!reader.HasRows);
                }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }

        public static bool ModifyPatClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Surname_1 = @codes WHERE Nit = @code";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return (!reader.HasRows);
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }

        public static bool ModifyMatClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Surname_2 = @codes WHERE Nit = @code";
            
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return (!reader.HasRows);
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }

        public static bool ModifyPhoneClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Telephone = @codes WHERE Nit = @code";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return (!reader.HasRows);
                    
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }

        public static bool ModifyCellClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Cellular = @codes WHERE Nit = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return (!reader.HasRows);
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }

        public static bool ModifyEmailClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Email = @codes WHERE Nit = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return !reader.HasRows;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
        public static double GetTotalMountForClient(string nitClient) {
            string sqlSentence = "Select CodeClient,SUM(TotalPriceSale) from Sale where CodeClient=@nit group by [CodeClient]";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable dt=new DataTable() ;
            try
            {
                dt.Locale= System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@nit", nitClient);
                SqlDataReader reader = cmd.ExecuteReader();
                dt.Load(reader);
                return Convert.ToDouble(dt.Rows[0].ItemArray[1],System.Globalization.CultureInfo.CurrentCulture);
            }
            finally
            {
                if (dt != null) {
                    dt.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        
        }
        public static bool ModifyAddressClient(string value, string nit)
        {
            string sqlSentence = "UPDATE Client SET Address = @codes WHERE Nit = @code";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            try
            {
                tempCnn = new SqlConnection(ConexionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@code", nit);
                cmd.Parameters.AddWithValue("@codes", value);
                SqlDataReader reader = cmd.ExecuteReader();
                return !reader.HasRows;
            }
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
            }


        }
    }
}
